import { Inject, Injectable } from '@nestjs/common';
import * as mysql from 'mysql'; // 引入mysql模块
import { ConfigService } from '@nestjs/config';
import { Logger } from 'src/utils/log4js';
import * as Chalk from 'chalk';
import * as ejs from 'ejs';
import {
  existsSync,
  writerStream,
  deleteFolderRecursive,
} from './file.operation';
import * as fs from 'fs';
import { zip } from 'compressing';

import { ToolsService } from 'src/utils/tools.service';

@Injectable()
export class DatabaseService {
  private pool: mysql.Pool = null;

  constructor(private readonly configService: ConfigService) {
    // 创建数据库连接池（可选，根据需要决定是否使用连接池）
    this.pool = mysql.createPool({
      ...this.configService.get('db.database'),
    });
  }

  connectToDatabase() {
    return new Promise((resolve, reject) => {
      this.pool.getConnection((err, connection) => {
        if (err) {
          reject(err); // 如果连接失败，则拒绝Promise并返回错误信息
        } else {
          resolve(connection); // 如果连接成功，则解析Promise并返回数据库连接对象
        }
      });
    });
  }

  executeQuery(query, params = []) {
    return new Promise((resolve, reject) => {
      this.connectToDatabase() // 动态建立数据库连接
        .then((connection: any) => {
          return connection.query(query, params, (error, results, fields) => {
            if (error) {
              reject(error);
              console.error('Error executing query:', error);
            } else {
              resolve(results);
              console.log('Query results:', results); // 处理查询结果
            }
            // 释放连接（重要！确保在查询完成后释放连接）
            connection.release(); // 使用连接池时释放连接回池中，以便其他请求可以使用。如果不是使用连接池，则关闭连接。
          });
        })
        .catch((error) => {
          reject(error);
          console.error('Error connecting to database:', error); // 处理连接错误
        });
    });
  }

  async createTable(params) {
    let sql = `
        drop table if exists ${params.name};
        `;
    let sql2 = `
     create table ${params.name}(
        \`uid\` bigint not null comment '唯一标识',
        ${params?.columns
          .map((item) => {
            return `${item.field} ${item.type} ${item.null ? '' : 'not null'} ${
              item.default ? 'default ' + item.default : ''
            } comment '${item.comment}'`;
          })
          .join(',')},
        \`create_time\` date  comment '创建时间',
        \`update_time\` date  comment '更新时间',
        primary key (\`uid\`)
      ) engine=innodb default charset=utf8mb4 comment ${
        "'" + params.comment + "'"
      };
         `;
    try {
      await this.executeQuery(sql);
      await this.executeQuery(sql2);
    } catch (error) {
      Logger.log(Chalk.red(`表创建失败！`), '\n', Chalk.red(error), '\n');
      ToolsService.fail(error);
    }

    return '表创建成功';
  }

  async getTableList() {
    let sql = `SELECT 
    TABLE_NAME as tableName, 
    CREATE_TIME as createTime,
    UPDATE_TIME as updateTime,
    ENGINE as engine,
    TABLE_ROWS as tableRows,
    DATA_LENGTH as dataLength,
    INDEX_LENGTH as indexLength,
    AUTO_INCREMENT as autoIncrement,
    TABLE_COMMENT as tableComment 
    FROM 
  information_schema.TABLES 
  WHERE 
  TABLE_SCHEMA = 'playdatabase'
  `;
    let res = null;
    try {
      res = await this.executeQuery(sql);
    } catch (error) {
      res = [];
      Logger.log(Chalk.red(`查询失败！`), '\n', Chalk.red(error), '\n');
      ToolsService.fail(error);
    }
    return res;
  }

  async getTableStructure(tableName: string) {
    let sql = `SELECT COLUMN_NAME as \`field\`, COLUMN_COMMENT as \`comment\`, COLUMN_TYPE as \`type\`, COLUMN_DEFAULT as \`default\`,IS_NULLABLE as \`null\`  
  FROM INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = '${tableName}';`;
    let res = null;

    try {
      res = await this.executeQuery(sql);
    } catch (error) {
      ToolsService.fail(error);
    }

    return res;
  }

  async removeTable(tableName: string) {
    let sql = `DROP TABLE IF EXISTS ${tableName};`;
    let res = null;
    try {
      res = await this.executeQuery(sql);
    } catch (error) {
      ToolsService.fail(error);
    }
    return res;
  }

  async addTableField(params) {
    // let field = 'field' + uuidv4().replace(/-/g, '');
    let sql = `ALTER TABLE ${params.tableName} ADD ${params.field}  ${
      params.type ? params.type : ''
    }  ${params.null == 'YES' ? '' : 'not null'}  ${
      params.default ? 'default ' + "'" + params.default + "'" : ''
    }   ${params.comment ? 'comment ' + "'" + params.comment + "'" : ''} ;`;
    let res = null;

    try {
      res = await this.executeQuery(sql);
    } catch (error) {
      ToolsService.fail(error);
    }
    return res;
  }

  async removeTableField(params) {
    // let field = 'field' + uuidv4().replace(/-/g, '');
    let sql = `ALTER TABLE ${params.tableName} DROP COLUMN ${params.field}`;
    let res = null;
    try {
      res = await this.executeQuery(sql);
    } catch (error) {
      ToolsService.fail(error);
    }
    return res;
  }

  async updateTableField(params) {
    // let field = 'field' + uuidv4().replace(/-/g, '');
    let sqlrm = `ALTER TABLE ${params.tableName} DROP COLUMN ${params.oldField}`;
    let sqlad = `ALTER TABLE ${params.tableName} ADD ${params.newField} ${
      params.type ? params.type : ''
    }  ${params.null == 'YES' ? '' : 'not null'}  ${
      params.default ? 'default ' + "'" + params.default + "'" : ''
    }   ${params.comment ? 'comment ' + "'" + params.comment + "'" : ''} ;`;
    try {
      await this.executeQuery(sqlrm);
      await this.executeQuery(sqlad);
    } catch (error) {
      ToolsService.fail(error);
    }
    return '更新成功';
  }

  async getTableAndFieldInfo() {
    let sql = `SELECT
          TABLE_NAME AS 'table',
          COLUMN_NAME AS 'column',
          COLUMN_COMMENT AS 'description'
        FROM
          INFORMATION_SCHEMA.COLUMNS
        WHERE
          TABLE_SCHEMA = 'playdatabase'
        ORDER BY
          TABLE_NAME,
          ORDINAL_POSITION`;
    let res = null;
    try {
      res = await this.executeQuery(sql);
    } catch (error) {
      ToolsService.fail(error);
    }
    return res;
  }

  async generateAndStreamFile(tableName: string) {
    let fieldInfo = null;
    let tarStream  = null;

   try {
    let sql = `SELECT COLUMN_NAME as \`field\`, COLUMN_COMMENT as \`comment\`, COLUMN_TYPE as \`type\`, COLUMN_DEFAULT as \`default\`,IS_NULLABLE as \`null\`  
    FROM INFORMATION_SCHEMA.COLUMNS
  WHERE   TABLE_NAME = '${tableName}';`;
    fieldInfo = await this.executeQuery(sql);
    await existsSync('./docflow/ejs-template');
    await existsSync('./docflow/await-download');
    let filename = 'field.ejs';
    const filePath = `./docflow/ejs-template/${filename}`;
    let data = fs.readFileSync(filePath, 'utf8');
    let list = fieldInfo,
      ejsdata = ejs.render(data, {
        list: list,
      });

    await existsSync('./docflow/await-download/' + tableName);
    await writerStream(
      './docflow/await-download/' + tableName + '/' + tableName + '.vue',
      ejsdata,
    );
    await writerStream(
      './docflow/await-download/' +
        tableName +
        '/' +
        tableName +
        '.controller.ts',
      ejsdata,
    );
    await writerStream(
      './docflow/await-download/' + tableName + '/' + tableName + '.service.ts',
      ejsdata,
    );
    await writerStream(
      './docflow/await-download/' + tableName + '/' + tableName + '.entity.ts',
      ejsdata,
    );
     tarStream = new zip.Stream(); //文件压缩流
    await tarStream.addEntry(
      process.cwd() + '/docflow/await-download/' + tableName,
    ); //压缩文件路径
   } catch (error) {
    ToolsService.fail(error);
   }
    return { tarStream };
  }
}
